USE fat1_c1203i_s3_g2
go
if object_id('sentEcard_temp') is not null
BEGIN
  drop table sentEcard_temp
END
SELECT * INTO sentEcard_temp FROM sentEcard WHERE DATEDIFF(dd,dateSent,GETDATE()) = 0
WHILE(SELECT COUNT(*) FROM sentEcard_temp) > 0  
BEGIN
	DECLARE
	@sentEcardId int,
	@customerId int,
	@ecardId int,
	@datesent datetime,
	@title varchar(100),
	@message_ecard varchar(1000),
	@signatures varchar(50),
	@nameSender varchar(50),
	@emailSender varchar(100)

	-- set gia tri cho cac availible
	select top 1 @sentEcardId = sentEcardId ,@customerId= customerId, @ecardId = ecardId, @datesent = dateSent , @title = title , @message_ecard = messages ,@signatures = signatures from sentEcard_temp

	select @nameSender = name ,@emailSender = email from customer where customerId = @customerId

	if object_id('listEmail_temp') is not null
		BEGIN
		  drop table listEmail_temp
		END
		SELECT * INTO listEmail_temp FROM listEmail WHERE sentEcardId = @sentEcardId
		while(SELECT COUNT(*) FROM listEmail_temp) > 0
		BEGIN
			DECLARE
			@emailReceived varchar(100),
			@nameReceived varchar(100),
		    @logSentMailId int,
			@message varchar(max),
			@subject_sent varchar(100)
			 -- set gia tri
			 select top 1 @emailReceived = emailReceived , @nameReceived = nameReceived from listEmail_temp
			 -- insert vao bang logsentmail de lay id
			 insert into logSentMail values(@customerID,@nameSender,@emailSender,@ecardId,GETDATE(),@title,@message_ecard,@signatures,@emailReceived,@nameReceived)
			 -- lay id cua cua thiep vua tao trong logsentmail de gui 
			select top 1 @logSentMailId =LogSentMailId from logSentMail where customerId = @customerID order by LogSentMailId desc 
			-- thiet lap thong  so gui mail
			SET @message ='['+@emailSender+'] just sent you an ecard from Ecard.com <br/>You can view it by clicking here:<br/> <a href="'+'http://localhost/Eproject_sem3_thanh/viewcardsent.aspx?id='+cast(@logSentMailId as varchar) +'">'+'http://localhost/Eproject_sem3_thanh/viewcardsent.aspx?id='+cast(@logSentMailId as varchar)+' </a>';
			
			Set @subject_sent = @emailSender +' sent you an ecard'

			EXEC msdb.dbo.sp_send_dbmail @profile_name='test profile',
			@recipients=@emailReceived,
			@subject=@subject_sent,
			@body=@message ,
			@body_format = 'HTML' 
			DELETE TOP (1) PERCENT FROM listEmail_temp 
		END
		-- update count cua ecard them 1
	update Ecard set counts = counts + 1 where ecardId = @ecardId
	DELETE TOP (1) PERCENT FROM sentEcard_temp 
END



